package com.metadata_system.common.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.alibaba.nacos.client.naming.utils.CollectionUtils;
import com.metadata_system.common.db.entity.ProjectNode;
import com.metadata_system.common.dto.ApiResult;
import com.metadata_system.common.utils.DateTimeUtil;
import com.metadata_system.common.utils.MyUtil;
import com.metadata_system.common.utils.RegexUtils;
import com.metadata_system.system.response.NodePropertiesItem;
import lombok.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.util.DigestUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Excel读取静态工具类
 * 支持从文件路径、MultipartFile和InputStream读取Excel文件
 */
public class ExcelReaderUtil {

    /**
     * 从文件路径读取Excel
     *
     * @param filePath 文件绝对路径
     * @return 所有sheet数据
     * @throws IOException 读取异常
     */
    public static List<ExcelSheetData> readExcel(String filePath) throws IOException {
        return readExcel(new File(filePath));
    }

    /**
     * 从File对象读取Excel
     *
     * @param file File对象
     * @return 所有sheet数据
     * @throws IOException 读取异常
     */
    public static List<ExcelSheetData> readExcel(File file) throws IOException {
        ExcelDataListener listener = new ExcelDataListener();
        EasyExcel.read(file, listener).headRowNumber(0).doReadAll();
        return listener.getSheetDataList();
    }

    /**
     * 从MultipartFile读取Excel（web上传文件）
     *
     * @param file 上传的文件
     * @return 所有sheet数据
     * @throws IOException 读取异常
     */
    public static List<ExcelSheetData> readExcel(MultipartFile file) throws IOException {
        return readExcel(file.getInputStream());
    }

    /**
     * 从输入流读取Excel
     *
     * @param inputStream 输入流
     * @return 所有sheet数据
     * @throws IOException 读取异常
     */
    public static List<ExcelSheetData> readExcel(InputStream inputStream) throws IOException {
        ExcelDataListener listener = new ExcelDataListener();
        EasyExcel.read(inputStream, listener).doReadAll();
        return listener.getSheetDataList();
    }

    /**
     * 内部监听器类，处理Excel读取逻辑
     */
    private static class ExcelDataListener implements ReadListener<Map<Integer, String>> {
        private final List<ExcelSheetData> sheetDataList = new ArrayList<>();
        private ExcelSheetData currentSheetData;
        private List<String> currentHeader = new ArrayList<>();
        private boolean isHeaderRead = false; // 标记当前sheet表头是否已读取
        private int currentSheetIndex = -1;
        private String currentSheetName;

        @Override
        public void invoke(Map<Integer, String> data, AnalysisContext context) {

            // 是否切换sheet
            if (currentSheetIndex != context.readSheetHolder().getSheetNo()) {
                isHeaderRead = false;
            }

            // 获取当前sheet信息
            currentSheetIndex = context.readSheetHolder().getSheetNo();
            currentSheetName = context.readSheetHolder().getSheetName();


            // 处理表头（只在每个sheet首次调用时执行）
            if (!isHeaderRead) {
                currentSheetData = new ExcelSheetData();
                currentSheetData.setIndex(currentSheetIndex);
                currentSheetData.setName(currentSheetName);
                currentSheetData.setErrorIndexList(new ArrayList<>());
                currentSheetData.setErrorPositionList(new ArrayList<>());
                currentSheetData.setErrorTotal(0);

                currentHeader = new ArrayList<>();

                // 头部单位剔除
                String regex = "\\s*(\\(|（)([^)]*?|[^）]*?)(\\)|）)\\s*";

                List<String> tmpHeader = new TreeMap<>(data).values().stream()
                        .map(value -> value != null ? value : "")
                        .collect(Collectors.toList());
                for (int i = 0; i < tmpHeader.size(); i++) {
                    String value = tmpHeader.get(i).replaceAll(regex, "");
                    if (!StringUtils.isBlank(tmpHeader.get(i)) && !currentHeader.contains(value)) {
                        currentHeader.add(value);
                    }
                }
//                currentHeader.sort(String::compareTo);

                System.out.println("currentHeader: " + JSON.toJSONString(currentHeader));

                // 从数据中获取表头（第一行作为表头）
                currentSheetData.setHeaders(JSON.parseArray(JSON.toJSONString(currentHeader), String.class));
                currentSheetData.setTableData(new ArrayList<>());


                // 设置table-header唯一标记
                currentSheetData.setHeaderKey(ProjectNode.getPropertiesKeyByNames(currentHeader, currentSheetName));
                isHeaderRead = true;
                sheetDataList.add(currentSheetData);
                return;
            }

            // 处理普通行数据
            List<String> rowData = new TreeMap<>(data).values().stream()
                    .map(value -> value != null ? value : "")
                    .collect(Collectors.toList());

            Map<String, Object> map = new HashMap<>();
            for (int i = 0; i < currentHeader.size(); i++) {
//                System.out.println("currentHeader: " + JSON.toJSONString(currentHeader));
//                System.out.println("rowData: " + JSON.toJSONString(rowData));
                if (rowData.size() - 1 >= i) {
                    map.put(currentHeader.get(i), rowData.get(i));
                }
            }

            currentSheetData.getTableData().add(map);

        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            // 解析完成后操作
        }

        //        @Override
        public void onSheetChanged(ReadSheet readSheet) {
            isHeaderRead = false; // 切换sheet时重置表头标记
        }

        public List<ExcelSheetData> getSheetDataList() {
            return sheetDataList;
        }
    }

    /**
     * 封装单个sheet的数据结构
     */
    @AllArgsConstructor
    @NoArgsConstructor
    @Builder
    @Data
    public static class ExcelSheetData {
        private int index;                                  // sheet索引
        private String name;                                // sheet名称
        private String headerKey;                            // 表头唯一键,使用【头部-字段-标题】排序生成
        private List<String> headers = new ArrayList<>();                         // 表头数据


        private List<NodePropertiesItem> tableFieldList;    // 表-字段-列表
        private List<Map<String, Object>> tableData;        // 表-数据
        private List<List<Integer>> errorPositionList;      // 错误位置列表
        private List<Integer> errorIndexList;               // 错误行
        private Integer errorTotal;                         // 错误行


        private String tableName;                           // 表-名称


        /**
         * 生成表头的唯一键
         *
         * @param headers 表头列表
         * @return 唯一键
         */
        public static String generateTitleKey(List<String> headers) {
            headers.sort(Comparator.naturalOrder());
            String str = JSON.toJSONString(headers);
            return DigestUtils.md5DigestAsHex(str.getBytes());
        }

        /**
         * 检查列表错误数据，坐标(行,列)
         *
         * @param excelSheetDataList
         */
        public static void checkErrorColumn(List<ExcelSheetData> excelSheetDataList) {
            for (ExcelSheetData excelSheetData : excelSheetDataList) {
                checkErrorColumn(excelSheetData);
            }
        }

        /**
         * 检查错误数据，坐标(行,列)
         *
         * @return
         */
        public static void checkErrorColumn(ExcelSheetData excelSheetData) {
            if (CollectionUtils.isEmpty(excelSheetData.getTableFieldList())) {
                return;
            }
            excelSheetData.errorPositionList = new ArrayList<>();

            Map<String, String> fieldNameTypeMap = new HashMap<>();
            excelSheetData.getTableFieldList().forEach(item -> {
                fieldNameTypeMap.put(item.getLabel(), item.getType());
            });
            System.out.println("fieldNameTypeMap: " + JSON.toJSONString(fieldNameTypeMap));

            List<Integer> errorIndexList = new ArrayList<>();
            for (int index = 0; index < excelSheetData.tableData.size(); index++) {
                Map<String, Object> row = excelSheetData.tableData.get(index);
                for (int _index = 0; _index < row.size(); _index++) {
                    String headerName = excelSheetData.headers.get(_index);
                    if (headerName.equalsIgnoreCase("ID")) {
                        continue;
                    }
                    String headerType = fieldNameTypeMap.get(headerName);

//                    Map<String,Object> value = row.get(_index);
//                    if (!RegexUtils.checkNodeProperties(value, headerType)) {
//                        System.out.println("================ 错误数据 ================");
//                        System.out.println("headerType: "+headerType);
//                        System.out.println("headerName: "+headerName);
//                        System.out.println("value: "+value);
//                        excelSheetData.errorPositionList.add(Arrays.asList(index, _index));
//                        if (!errorIndexList.isEmpty() && !errorIndexList.contains(index)) {
//                            errorIndexList.add(index);
//                        }
//                    }
                }
            }

            System.out.println("excelSheetData.errorPositionList:" + JSON.toJSONString(excelSheetData.errorPositionList));
            excelSheetData.setErrorIndexList(errorIndexList);
            excelSheetData.setErrorTotal(errorIndexList.size());

        }
    }

    public static List<ExcelSheetData> getFileContent(String filePath) {
        // 1.读取文件内容
        String fileContent = MyUtil.readFile(filePath);
        if (StringUtils.isBlank(fileContent)) {
            throw new RuntimeException("文件内容 为空");
        }
        // 2.解析文件内容
        List<ExcelSheetData> excelSheetDataList = JSON.parseArray(fileContent, ExcelSheetData.class);
        if (CollectionUtils.isEmpty(excelSheetDataList)) {
            throw new RuntimeException("文件内容 为空");
        }
        return excelSheetDataList;
    }


    /**
     * 导出Excel文件
     * @param excelSheetDataList  数据列表
     * @param exportExcelFilePath  导出的Excel文件路径
     * @return
     */
    public static ApiResult exportExcel(List<ExcelSheetData> excelSheetDataList, String exportExcelFilePath) {

        // 1.构造写入数据
        List<List<Map<String, Object>>> sheetDataList = new ArrayList<>();
        for (ExcelSheetData sheetData : excelSheetDataList) {
            List<Map<String, Object>> tableDataList = sheetData.getTableData();
            List<Map<String, Object>> newTableDataList = new ArrayList<>();

            Map<String,String> propNameMap = new HashMap<>();
            for (NodePropertiesItem nodePropertiesItem : sheetData.getTableFieldList()) {
                propNameMap.put(nodePropertiesItem.getProp(),nodePropertiesItem.getLabel());
            }
            propNameMap.put("id","序号");
            propNameMap.put("projectLogId","项目日志ID");
            propNameMap.put("createdTime","创建时间");

            // 构造新的tableDataList
            for (Map<String, Object> map : tableDataList) {
                Map<String, Object> newMap = new HashMap<>();
                for (String prop : map.keySet()) {
//                    if(prop.equalsIgnoreCase("projectlogid") || prop.equalsIgnoreCase("create")) {
                    if (prop.equals("createdTime")) {
                        newMap.put("createdTime", DateTimeUtil.formatDateTimetoString((Date) map.get(prop),DateTimeUtil.FMT_yyyyMMddHHmmss));
                    }else{
                        newMap.put(propNameMap.getOrDefault(prop,prop),map.get(prop));
                    }
                }
                newTableDataList.add(newMap);
            }

            sheetData.setTableData(newTableDataList);
        }



        // 2. 创建Excel写入器
        try (var excelWriter = EasyExcel.write(exportExcelFilePath)
                .registerWriteHandler(createHeaderStyleStrategy())
                .build()) {
            for (ExcelSheetData sheetData : excelSheetDataList) {
                WriteSheet sheet = createSheet(sheetData.getName(), sheetData.getTableData().get(0).keySet());
                System.out.println("sheetData.getTableData(): "+JSON.toJSONString(sheetData.getTableData()));
                List<List<Object>> data = convertMapToData(sheetData.getTableData(), sheetData.getTableData().get(0).keySet());
//                excelWriter.write(data1, sheet1);
                excelWriter.write(data, sheet);
            }
        }

        return ApiResult.success(sheetDataList);
    }

    /**
     * 创建Sheet并设置表头
     */
    private static WriteSheet createSheet(String sheetName, Set<String> headers) {
        List<List<String>> head = new ArrayList<>();
        for (String header : headers) {
            List<String> headColumn = new ArrayList<>();
            headColumn.add(header);
            head.add(headColumn);
        }

        return EasyExcel.writerSheet(sheetName)
                .head(head)
                .build();
    }

    /**
     * 将Map数据转换为EasyExcel需要的格式
     */
    private static List<List<Object>> convertMapToData(List<Map<String, Object>> mapList, Set<String> headers) {
        List<List<Object>> data = new ArrayList<>();
        for (Map<String, Object> map : mapList) {
            List<Object> row = new ArrayList<>();
            // 按照指定的headers顺序来获取值，确保列顺序一致
            for (String header : headers) {
                row.add(map.get(header));
            }
            data.add(row);
        }
        return data;
    }

    /**
     * 创建表头样式策略
     */
    private static HorizontalCellStyleStrategy createHeaderStyleStrategy() {
        // 表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();

        // 设置表头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体"); // 设置字体
        headWriteFont.setFontHeightInPoints((short) 11); // 设置字体大小为12px（实际是12磅）
        headWriteFont.setBold(true); // 设置粗体

        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 居中对齐
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 设置背景色

        // 内容样式（可选）
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    @SneakyThrows
    public static void main(String[] args) {
        List<ExcelSheetData> excelSheetDataList = getFileContent("C:\\Users\\Administrator\\Desktop\\metadata-system-api\\file\\upload-file.cc\\import-file\\UxSHygu.json");
        exportExcel(excelSheetDataList,"C:\\Users\\Administrator\\Desktop\\d1.xls");
//        List<String> currentHeader = Arrays.asList("id", "NAME", "AGE", "SEX");
//        System.out.println("res: " + currentHeader.stream()
//                .filter(header -> !"ID".equals(header.toUpperCase()))
//                .collect(Collectors.toList()));

//        List<ExcelSheetData> excelSheetData = ExcelReaderUtil.readExcel("C:\\Users\\Administrator\\Desktop\\test.xls");
//        ExcelReaderUtil.ExcelSheetData.checkErrorColumn(excelSheetData);
//        excelSheetData.forEach(sheetData -> {
//            System.out.println("sheetData.errorTotal: " + sheetData.errorTotal);
//        });

//        List<String> list = Arrays.asList("e","a", "b", "c");
//        list.sort(Comparator.naturalOrder());
//        System.out.println(JSON.toJSONString(list));
    }


}
